package com.xisoft.devise.service.impl;

import cn.hutool.core.collection.ListUtil;
import cn.hutool.core.map.MapUtil;
import com.xisoft.base.service.XfServiceImpl;
import com.xisoft.common.*;
import com.xisoft.common.constant.IPubCons;
import com.xisoft.common.constant.ISqlCons;
import com.xisoft.common.constant.InumberCons;
import com.xisoft.common.db.DbTypeConvert;
import com.xisoft.common.db.SqlHelper;
import com.xisoft.common.io.FileUtilbc;
import com.xisoft.common.pubEnv.*;
import com.xisoft.devise.entity.metadata.DatacodeVO;
import com.xisoft.devise.entity.metadata.DicTableFieldVO;
import com.xisoft.devise.entity.metadata.DicTableVO;
import com.xisoft.devise.mapper.IMetadataMapper;
import com.xisoft.devise.service.IMetadataService;
import lombok.Getter;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import java.io.File;
import java.io.IOException;
import java.util.*;
import java.util.concurrent.atomic.AtomicInteger;
import java.util.concurrent.atomic.AtomicReference;

/**
 * *
 * Created with Description:
 *
 * @version 1.0 <br>
 * @packagename: com.xisoft.devise.service.impl<br>
 * @author: LiBencheng <br>
 * @computer: licheng <br>
 * @date: Created on 2022/8/25 18:26 <br>
 */
@Service ("MetadataServiceImpl")
@Transactional (rollbackFor = Exception.class)
public class MetadataServiceImpl extends XfServiceImpl implements IMetadataService
{
    @Autowired
    @Getter
    private IMetadataMapper mapper;


    @Transactional @Override public String savaSysDataCode(String json) throws Exception
    {
        Map< String,Object > tabMap = JsonUtil.toMap(json);
        HashMap< String,Object > dataCodeMap = MapUtil.newHashMap();

        dataCodeMap.put("id", GenerateUtil.generateID());
        // dataCodeMap.put("flag", tabMap.get("1"));
        dataCodeMap.put("code", tabMap.get("code"));
        dataCodeMap.put("value", tabMap.get("value"));
        dataCodeMap.put("jp", PinYinUtil.convertSimpleSpelling(StringUtil.valueOfEmpty(dataCodeMap.get("value"))));
        dataCodeMap.put("description", tabMap.get("description"));
        dataCodeMap.put("pcode", tabMap.get("pcode"));
        if (PubEnvUtil.equals("solution", tabMap.get("type")))
        { dataCodeMap.put("pcode", IPubCons.DASH); }
        dataCodeMap.put("type", tabMap.get("SystemCode"));

        String insertSQL = SqlHelper.getInsertSQL(DatacodeVO.TABLE_NAME, dataCodeMap);
        LoggerUtil.getInstance().debug(insertSQL);
        // getMapper().exeUpdateSql(insertSQL);
        return "操作成功！insertSQL：" + insertSQL;
    }

    @Transactional @Override public String savaScript(String json) throws Exception
    {
        MetadataService meta = new MetadataService();
        Map< String,Object > tabMap = JsonUtil.toMap(json);
        CheckUtil.checkMetaTable(tabMap);
        StringBuffer sqlScript = new StringBuffer();
        StringBuffer createSqlScript = new StringBuffer();
        StringBuffer insertSqlScript = new StringBuffer();
        insertSqlScript.append("\n-- 自动生成sql脚本 日期：").append(DateUtilbc.currentDateTime())
                       .append(IPubCons._N).append(IPubCons.CRLF);
        insertSqlScript.append(" DECLARE tableExistedCount number; recordnum number;  BEGIN ").append(IPubCons.CRLF);
        sqlScript.append("\n-- 自动生成sql脚本 日期：").append(DateUtilbc.currentDateTime()).append(IPubCons.CRLF);
        List< String > insertList = ListUtil.list(Boolean.TRUE);

        tabMap.put(DicTableVO.PRIMARY_KEY, GenerateUtil.generateID());
        List< Map< String,Object > > metadataList = (List< Map< String,Object > >) tabMap.get("fields");
        if (PubEnvUtil.getSize(metadataList) > 0)
        {
            StringBuffer commentSqlScript = new StringBuffer();
            StringBuffer alterSqlScript = new StringBuffer();
            // sqlScript.append(String.format(ISqlCons.dropTableSql, tabMap.get(DicTableVO.SDT_TABLE_EN_NAME)));
            createSqlScript.append(" CREATE TABLE ")
                           .append(tabMap.get(DicTableVO.SDT_TABLE_EN_NAME))
                           .append(PubEnvUtil.LEFT_BRACKET).append(PubEnvUtil.CRLF);
            AtomicReference primary_key = new AtomicReference<>("");
            metadataList.stream()
                        .forEach(fieldMap ->
                                 {
                                     meta.setDef(tabMap, fieldMap);
                                     CheckUtil.checkMetaField(fieldMap);
                                     remove(fieldMap);
                                     tabMap.put(DicTableFieldVO.PRIMARY_KEY, GenerateUtil.generateID());
                                     String insertSQL = SqlHelper.getInsertSQL(DicTableFieldVO.TABLE_NAME, fieldMap);
                                     insertList.add(insertSQL);
                                     meta.createTableScript(fieldMap, createSqlScript, commentSqlScript);
                                     meta.alterTableScript(tabMap, fieldMap, alterSqlScript);
                                     // if (PubEnvUtil.equals("DXZJ",  fieldMap.get(DicTableFieldVO.SDF_FIELD_TYPE)))
                                     if (PubEnvUtil.equals(IPubCons.YES, StringUtil.toString(fieldMap.get(
                                             DicTableFieldVO.SDF_PRIMARY_KEY)).toLowerCase()))
                                     { primary_key.set(fieldMap.get(DicTableFieldVO.SDF_FIELD_EN_NAME)); }
                                     meta.insertFieldSqlScript(fieldMap, insertSqlScript, insertSQL);
                                 });
            // meta.createPrefabField(tabMap, sqlScript, commentSqlScript);
            createSqlScript.append(IPubCons.TAB).append(" PRIMARY KEY (")
                           .append(primary_key.get())
                           .append(PubEnvUtil.RIGHT_BRACKET).append(PubEnvUtil.RIGHT_BRACKET)
                           .append(PubEnvUtil.SEMICOLON).append(IPubCons.CRLF);
            //commentS
            sqlScript = meta.parseConfigHead(tabMap, StringUtil.toString(createSqlScript));
            sqlScript.append(IPubCons.CRLF).append(commentSqlScript);
            sqlScript.append("  END IF; ").append(IPubCons.CRLF);
            sqlScript.append(IPubCons.CRLF).append(alterSqlScript);
            remove(tabMap);
            tabMap.put(DicTableVO.PRIMARY_KEY, GenerateUtil.generateID());
        }
        sqlScript.append(" commit; end;").append(IPubCons.CRLF);
        sqlScript.append(LoggerUtil.getSplitLine());

        StringBuffer msg = new StringBuffer();
        String pathname = getClassPatch() + tabMap.get(DicTableVO.SDT_TABLE_EN_NAME) + ".sql";
        msg.append(pathname).append(IPubCons._N);
        // 生成修改表脚本，
        FileUtilbc.write(new File(pathname), StringUtil.toString(sqlScript), PubEnvUtil.UTF_8);

        meta.insertTableSqlScript(tabMap, insertSqlScript, SqlHelper.getInsertSQL(DicTableVO.TABLE_NAME, tabMap));
        pathname = getClassPatch() + "新增元数据表(" + tabMap.get(DicTableVO.SDT_TABLE_EN_NAME) + ").sql";
        msg.append(pathname).append(IPubCons._N);
        insertSqlScript.append(" commit; end;").append(IPubCons.CRLF);
        insertSqlScript.append(LoggerUtil.getSplitLine()).append(IPubCons.CRLF);
        // 生成修改表脚本，
        FileUtilbc.write(new File(pathname), StringUtil.toString(insertSqlScript), PubEnvUtil.UTF_8);

        AtomicInteger ret = new AtomicInteger(0);
        meta.createReserveFieldSql(tabMap, insertList);
        insertList.add(SqlHelper.getInsertSQL(DicTableVO.TABLE_NAME, tabMap));
        // 保存元数据字段
        if (PubEnvUtil.getSize(insertList) > 0)
        {
            LoggerUtil.debug(insertList);
            pathname = getClassPatch() + "(" + tabMap.get(DicTableVO.SDT_TABLE_EN_NAME) + ")新增元数据表.sql";
            msg.append(pathname).append(IPubCons._N);
            // 生成修改表脚本，
            // FileUtilbc.write(new File(pathname), StringUtil.toString(insertList), PubEnvUtil.UTF_8);

            insertList.stream().forEach(sql ->
                                        {
                                            // ret.addAndGet(mapper.exeUpdateSql(sql));
                                        });
        }

        return StringUtil.toString(ret);
    }


    String getClassPatch( ) throws IOException
    {
        return "D:\\OneDrive\\sqlScript\\";
        // String filePath = this.getClass().getResource("/application.properties").getPath();
        // Map< String,String > proMap = PropertiesUtil.getAllProperties(filePath);
        //
        // return proMap.get("classpatch");
    }

    @Override public Collection getMetadataInfo( )
    {
        List< Map< String,Object > > dataCode = mapper.getSysDataCode();
        Map< String,Object > groupMap = CollectionUtil.receiveGroupMap(dataCode, DatacodeVO.CODE, null);
        List< Map< String,Object > > children = new LinkedList<>();
        Map< String,Map< String,Object > > retMap = new LinkedHashMap<>();
        for (Map< String,Object > map : dataCode)
        {
            if (PubEnvUtil.equals(map.get(DatacodeVO.PCODE), PubEnvUtil.DASH))
            { retMap.put(StringUtil.toString(map.get(DatacodeVO.CODE)), map); }
            else
            {
                Map< String,Object > objMap = retMap.get(map.get(DatacodeVO.PCODE));
                if (PubEnvUtil.isNullObj(objMap))
                {
                    // 这里只有两层处理，多层需要递归，没有处理
                    objMap = (Map< String,Object >) groupMap.get(map.get(DatacodeVO.PCODE));
                    children = (List< Map< String,Object > >) (objMap.get("children"));
                    if (PubEnvUtil.getSize(children) < 1) { children = new LinkedList<>(); }
                    children.add(map);
                    List< Map< String,Object > > tempList =
                            (List< Map< String,Object > >) (retMap.get(objMap.get(DatacodeVO.PCODE))
                                                                  .get("children"));
                    for (Map< String,Object > child : tempList)
                    {
                        if (PubEnvUtil.equals(child.get(map.get(DatacodeVO.CODE)),
                                              objMap.get(map.get(DatacodeVO.CODE))))
                        { child.put("children", children); }
                    }

                }
                else
                {
                    children = (List< Map< String,Object > >) (objMap.get("children"));
                    if (PubEnvUtil.getSize(children) < 1) { children = new LinkedList<>(); }
                    children.add(map);
                    retMap.get(map.get(DatacodeVO.PCODE)).put("children", children);
                }
            }
        }

        return retMap.values();
    }

    @Override public Map< String,Object > getSysTabByTable(String table) throws Exception
    {
        Map< String,Object > tableMap = null;
        table = table.trim().toLowerCase(Locale.ROOT);
        List< Map< String,Object > > tableList = mapper.getSysTableByCode(table);
        if (PubEnvUtil.getSize(tableList) > 0)
        {
            tableMap = tableList.get(0);
            tableMap.put("fields", getSysTableField(table));
        }
        return tableMap;
    }

    public Map< String,Object > getSysTabByTable2(String table) throws Exception
    {
        Map< String,Object > tableMap = null;
        table = table.trim().toLowerCase(Locale.ROOT);
        List< Map< String,Object > > tableList = mapper.getSysTableByCode2(table);
        if (PubEnvUtil.getSize(tableList) > 0)
        {
            tableMap = tableList.get(0);
            tableMap.put("fields", mapper.getSysTableField2(table));
        }
        return tableMap;
    }

    @Override public Map< String,Object > getSysTableByCode(String table) throws Exception
    {
        Map< String,Object > tableMap = null;

        String sql = " SELECT  id,pcode, type,code, value, cindex FROM sys_datacode d " +
                "WHERE id = 'fe5c81bf-f8d8-4ebf-82cd-2bc54c0a4dfa' Order by pcode";
        List< Map > data = mapper.getDataBySql(sql);
        tableMap = data.get(0);
        sql = "SELECT  id,pcode, type,code, value, cindex FROM sys_datacode d WHERE pcode='folder_3'";
        data = mapper.getDataBySql(sql);
        tableMap.put("children", data);
        Map< String,Object > dataMap = null;
        BxTableService bxTablService = new BxTableService();
        for (int x = 0; x < PubEnvUtil.getSize(data); x++)
        {
            Map< String,Object > temp = data.get(x);
            if (PubEnvUtil.equals("bx", temp.get("code")))
            {
                ArrayList< Object > obj = new ArrayList<>();
                bxTablService.getBx(obj);
                bxTablService.getApply(obj);
                bxTablService.getLoan(obj);
                // bxTablService.setxzhv(obj);
                temp.put("children", obj);
                data.set(x, temp);
            }
        }
        String pathname = getClassPatch() + "bx.json";
        // 生成修改表脚本，
        FileUtilbc.write(new File(pathname), JsonUtil.transObj2Josn(tableMap), PubEnvUtil.UTF_8);

        return tableMap;
    }

    @Override public List< Map< String,Object > > getSysTable(String syscode)
    { return mapper.getSysTable(syscode); }

    @Override public List< Map< String,Object > > getSysTableField(String table)
    { return mapper.getSysTableField(table); }


    /************************************************************* <br>
     *
     *说明: 移除数据库不存在的字段 <br>
     *
     * @Description   <br>
     * @Date Created on 2022/9/2  15:21  <br>
     * @param map <br>
     * @return void  <br>
     * @Author LIBENCHENG
     * @version 1.0 <br>
     ************************************************************* <br>
     */
    private void remove(Map< String,Object > map)
    {
        map.remove("fields");
        map.remove("isexist");
    }

    public String savaScriptBak(String json) throws Exception
    {
        MetadataService meta = new MetadataService();
        Map< String,Object > tabMap = JsonUtil.toMap(json);
        CheckUtil.checkMetaTable(tabMap);
        StringBuffer sqlScript = new StringBuffer();
        sqlScript.append("\n-- 自动生成sql脚本 日期：").append(DateUtilbc.currentDateTime())
                 .append(IPubCons.CRLF);
        List< String > insertList = ListUtil.list(Boolean.TRUE);
        if (PubEnvUtil.equals(PubEnvUtil.TRUE, tabMap.get("isexist")))
        {
            List< Map< String,Object > > metadataList = (List< Map< String,Object > >) tabMap.get("fields");

            if (PubEnvUtil.getSize(metadataList) > 0)
            {
                metadataList
                        .stream()
                        .forEach(fieldMap ->
                                 {
                                     meta.updateTableScript(fieldMap, sqlScript);
                                     CheckUtil.checkMetaField(fieldMap);
                                     if (PubEnvUtil.equals(PubEnvUtil.TRUE, fieldMap.get("isexist")))
                                     {
                                         fieldMap.put(DicTableFieldVO.MODIFIEDON, DateUtilbc.currentDateTime());
                                         insertList.add(SqlHelper.getUpdateByIdSQL(DicTableFieldVO.TABLE_NAME
                                                 , fieldMap, DicTableFieldVO.PRIMARY_KEY));
                                     }
                                     else
                                     {
                                         meta.setDef(tabMap, fieldMap);
                                         insertList.add(SqlHelper.getInsertSQL(DicTableFieldVO.TABLE_NAME, fieldMap));
                                     }
                                 });
            }
            sqlScript.append(LoggerUtil.getSplitLine());
        }
        else
        {
            tabMap.put(DicTableVO.PRIMARY_KEY, GenerateUtil.generateID());
            List< Map< String,Object > > metadataList = (List< Map< String,Object > >) tabMap.get("fields");
            if (PubEnvUtil.getSize(metadataList) > 0)
            {
                StringBuffer commentSqlScript = new StringBuffer();
                sqlScript.append(String.format(ISqlCons.dropTableSql, tabMap.get(DicTableVO.SDT_TABLE_EN_NAME)));
                sqlScript.append(" CREATE TABLE ")
                         .append(tabMap.get(DicTableVO.SDT_TABLE_EN_NAME))
                         .append(PubEnvUtil.LEFT_BRACKET).append(PubEnvUtil.CRLF);
                AtomicReference primary_key = new AtomicReference<>("");
                metadataList.stream()
                            .forEach(fieldMap ->
                                     {
                                         meta.setDef(tabMap, fieldMap);
                                         CheckUtil.checkMetaField(fieldMap);
                                         remove(fieldMap);
                                         insertList.add(SqlHelper.getInsertSQL(DicTableFieldVO.TABLE_NAME, fieldMap));
                                         meta.createTableScript(fieldMap, sqlScript, commentSqlScript);
                                         // if (PubEnvUtil.equals("DXZJ", fieldMap.get(DicTableFieldVO.SDF_FIELD_TYPE)))
                                         if (PubEnvUtil.equals(IPubCons.YES, StringUtil.toString(fieldMap.get(
                                                 "sdf_primary_key")).toLowerCase()))
                                         { primary_key.set(fieldMap.get(DicTableFieldVO.SDF_FIELD_EN_NAME)); }
                                     });
                // meta.createPrefabField(tabMap, sqlScript, commentSqlScript);
                sqlScript.append(" PRIMARY KEY (").append(primary_key.get()).append(PubEnvUtil.RIGHT_BRACKET)
                         .append(PubEnvUtil.RIGHT_BRACKET).append(PubEnvUtil.SEMICOLON).append(IPubCons.CRLF);
                //commentS
                sqlScript.append(IPubCons.CRLF).append(commentSqlScript);
                remove(tabMap);
            }
            sqlScript.append(LoggerUtil.getSplitLine());
        }
        // 生成修改表脚本，
        FileUtilbc.writeAppend(new File(getClassPatch() + tabMap.get(DicTableVO.SDT_TABLE_EN_NAME) + ".sql")
                , StringUtil.toString(sqlScript), PubEnvUtil.UTF_8);

        AtomicInteger ret = new AtomicInteger(0);
        meta.createReserveFieldSql(tabMap, insertList);
        insertList.add(SqlHelper.getInsertSQL(DicTableVO.TABLE_NAME, tabMap));
        // 保存元数据字段
        if (PubEnvUtil.getSize(insertList) > 0)
        {
            LoggerUtil.debug(insertList);
            insertList.stream().forEach(sql ->
                                        {
                                            // ret.addAndGet(mapper.exeUpdateSql(sql));
                                        });
        }

        return StringUtil.toString(ret);
    }

    class BxTableService
    {
        //报销单
        private void getBx(ArrayList< Object > obj) throws Exception
        {
            String table = "bx_refund_account";
            String children = "bx_refund_account_detail," +
                    "bx_travel_partner," +
                    "bx_payment_details," +
                    "bx_bra_bb_rule," +
                    "bx_refund_cost_share_detail," +
                    "bx_refund_cost_allocation_rule," +
                    "bx_refund_cost_allocation," +
                    "bx_sterilistate_detail";
            String reference = "";
            reference = "sys_datacode,sys_organization,bd_arap_business_type," +
                    "sys_user,spm_pro_register,cbm_budget_finalversion,spm_budget_target";

            setTableInfo(obj, table, children, reference);

        }

        private void getApply(ArrayList< Object > obj) throws Exception
        {
            String table = "bx_bill";
            String children = "bx_cost_apply_detail,bx_travel_partner,bx_cost_share_detail,";
            String reference = "";
            reference = "sys_datacode,sys_organization,bd_arap_business_type," +
                    "sys_user,spm_pro_register,cbm_budget_finalversion,spm_budget_target";

            setTableInfo(obj, table, children, reference);
        }

        private void getLoan(ArrayList< Object > obj) throws Exception
        {
            String table = "bx_borrow_bill";
            String children = "bx_payment_details,bx_sterilistate_detail";
            String reference = "";
            setTableInfo(obj, table, children, reference);
        }

        private void setxzhv(ArrayList< Object > obj) throws Exception
        {
            String table = "bx_bill";
            String children = "bx_bill_detail,bx_travel_partner," +
                    "bx_payment_details,bx_cost_apply_detail,bx_cost_share_detail";
            String reference = "";
            setTableInfo(obj, table, children, reference);
        }

        private void setTableInfo(ArrayList< Object > obj, String table, String children, String reference) throws Exception
        {
            Map< String,Object > dataMap = null;
            Map< String,Object > tableMap = null;
            dataMap = getSysTabByTable(table.trim());

            List< Map< String,Object > > tableList = new LinkedList<>();
            String[] split = children.split(PubEnvUtil.COMMA);
            for (int i = 0; i < PubEnvUtil.getSize(split); i++)
            {
                tableMap = getSysTabByTable(split[i].trim());
                if (PubEnvUtil.getSize(tableMap) > 0)
                { tableList.add(tableMap); }
            }
            if (PubEnvUtil.getSize(split) > 0)
            { dataMap.put("children", tableList); }

            tableList = new LinkedList<>();
            split = reference.split(PubEnvUtil.COMMA);
            for (int i = 0; i < PubEnvUtil.getSize(split); i++)
            {
                tableMap = getSysTabByTable(split[i].trim());
                if (PubEnvUtil.getSize(tableMap) > 0)
                { tableList.add(tableMap); }
            }
            if (PubEnvUtil.getSize(split) > 0)
            { dataMap.put("reference", tableList); }

            obj.add(dataMap);
        }

    }

    class MetadataService
    {
        private void alterTableScript(Map< String,Object > tabMap, Map< String,Object > fieldMap, StringBuffer alterSql)
        {
            alterSql.append(" SELECT COUNT(1) into recordnum FROM user_tab_cols ");
            alterSql.append(" WHERE (table_name = '")
                    .append(StringUtil.toString(tabMap.get(DicTableVO.SDT_TABLE_EN_NAME)).toLowerCase())
                    .append("' OR table_name='")
                    .append(StringUtil.toString(tabMap.get(DicTableVO.SDT_TABLE_EN_NAME)).toUpperCase());
            alterSql.append("')  AND (column_name='")
                    .append(StringUtil.toString(fieldMap.get(DicTableFieldVO.SDF_FIELD_EN_NAME)).toLowerCase())
                    .append("' OR column_name='")
                    .append(StringUtil.toString(fieldMap.get(DicTableFieldVO.SDF_FIELD_EN_NAME)).toUpperCase())
                    .append("'); ").append(IPubCons._N);
            alterSql.append("     IF recordnum <= 0 THEN ").append(IPubCons._N);
            alterSql.append(parseExeImmediate(String.format(ISqlCons.addTableSql
                            , fieldMap.get(DicTableFieldVO.SDT_TABLE_EN_NAME)
                            , fieldMap.get(DicTableFieldVO.SDF_FIELD_EN_NAME)
                            , DbTypeConvert.collateDbType(StringUtil.toString(fieldMap.get(DicTableFieldVO.SDF_FIELD_TYPE))
                                    , StringUtil.toString(fieldMap.get(DicTableFieldVO.SDF_FIELD_LENGTH))
                                    , StringUtil.toString(fieldMap.get(DicTableFieldVO.SDF_PRIMARY_KEY))))))
                    .append(IPubCons._N);

            alterSql.append("  END IF; ").append(IPubCons._N);
        }

        private void updateTableScript(Map< String,Object > fieldMap, StringBuffer sqlScript)
        {
            if (PubEnvUtil.equals(PubEnvUtil.TRUE, fieldMap.get("isexist")))
            {
                sqlScript.append(String.format(ISqlCons.addTableSql
                        , fieldMap.get(DicTableFieldVO.SDT_TABLE_EN_NAME)
                        , fieldMap.get(DicTableFieldVO.SDF_FIELD_EN_NAME)
                        , DbTypeConvert.collateDbType(StringUtil.toString(fieldMap.get(DicTableFieldVO.SDF_FIELD_TYPE))
                                , StringUtil.toString(fieldMap.get(DicTableFieldVO.SDF_FIELD_LENGTH))
                                , StringUtil.toString(fieldMap.get(DicTableFieldVO.SDF_PRIMARY_KEY)))
                ));
            }
            sqlScript.append(String.format(ISqlCons.commentSql
                    , fieldMap.get(DicTableFieldVO.SDT_TABLE_EN_NAME)
                    , fieldMap.get(DicTableFieldVO.SDF_FIELD_EN_NAME)
                    , fieldMap.get(DicTableFieldVO.SDF_FIELD_ZH_NAME)));
        }

        private void insertTableSqlScript(Map< String,Object > fieldMap, StringBuffer insertSqlScript, String insertSQL)
        {
            insertSqlScript.append(" SELECT COUNT(1) into recordnum FROM sys_dic_table ").append(IPubCons._N);
            insertSqlScript.append(" WHERE sdt_table_en_name='")
                           .append(fieldMap.get(DicTableVO.SDT_TABLE_EN_NAME)).append("'").append(IPubCons._N);
            insertSqlScript.append(" AND sdt_system_name='")
                           .append(fieldMap.get(DicTableVO.SDT_SYSTEM_NAME)).append("'").append(IPubCons._N);
            insertSqlScript.append(" AND sdt_table_type='")
                           .append(fieldMap.get(DicTableVO.SDT_TABLE_TYPE)).append("';").append(IPubCons._N);

            insertSqlScript.append("     IF recordnum <= 0 THEN ").append(IPubCons._N);
            insertSqlScript.append(parseExeImmediate(insertSQL)).append(IPubCons._N);
            insertSqlScript.append("  END IF;").append(IPubCons._N).append(IPubCons._N);
        }

        private void insertFieldSqlScript(Map< String,Object > fieldMap, StringBuffer insertSqlScript, String insertSQL)
        {
            insertSqlScript.append(" SELECT COUNT(1) into recordnum FROM sys_dic_table_field ").append(IPubCons._N);
            insertSqlScript.append(" WHERE sdf_field_en_name='")
                           .append(fieldMap.get(DicTableFieldVO.SDF_FIELD_EN_NAME)).append("'").append(IPubCons._N);
            insertSqlScript.append(" AND sdt_table_en_name='")
                           .append(fieldMap.get(DicTableFieldVO.SDT_TABLE_EN_NAME)).append("'").append(IPubCons._N);
            insertSqlScript.append(" AND sdt_system_name='")
                           .append(fieldMap.get(DicTableFieldVO.SDT_SYSTEM_NAME)).append("';").append(IPubCons._N);

            insertSqlScript.append("     IF recordnum <= 0 THEN ").append(IPubCons._N);
            insertSqlScript.append(parseExeImmediate(insertSQL)).append(IPubCons._N);
            insertSqlScript.append("  END IF;").append(IPubCons._N).append(IPubCons._N);
        }

        private void createTableScript(Map< String,Object > fieldMap, StringBuffer sqlScript,
                StringBuffer commentSqlScript)
        {
            // 字段名 VARCHAR2(36) NOT NULL,
            sqlScript.append(PubEnvUtil.TAB).append(PubEnvUtil.TAB)
                     .append(fieldMap.get(DicTableFieldVO.SDF_FIELD_EN_NAME)
                                     + DbTypeConvert.collateDbType(StringUtil.toString(fieldMap.get(DicTableFieldVO.SDF_FIELD_TYPE))
                             , StringUtil.toString(fieldMap.get(DicTableFieldVO.SDF_FIELD_LENGTH))
                             , StringUtil.toString(fieldMap.get(DicTableFieldVO.SDF_PRIMARY_KEY)))
                     ).append(PubEnvUtil.COMMA).append(IPubCons.CRLF);

            commentSqlScript.append(parseExeImmediate(String.format(ISqlCons.commentSql
                    , fieldMap.get(DicTableFieldVO.SDT_TABLE_EN_NAME)
                    , fieldMap.get(DicTableFieldVO.SDF_FIELD_EN_NAME)
                    , fieldMap.get(DicTableFieldVO.SDF_FIELD_ZH_NAME)))).append(IPubCons.CRLF);
        }

        private StringBuffer parseConfigHead(Map< String,Object > tabMap, String statement)
        {
            StringBuffer head = new StringBuffer();
            head.append(" DECLARE ").append(IPubCons._N);
            head.append("   tableExistedCount number; ").append(IPubCons._N);
            head.append("   recordnum number; ").append(IPubCons._N);
            head.append(" BEGIN ").append(IPubCons._N);
            head.append(" SELECT COUNT(1) INTO tableExistedCount FROM user_tables t");
            head.append(" WHERE t.table_name = UPPER('");
            head.append(tabMap.get(DicTableVO.SDT_TABLE_EN_NAME));
            head.append("'); ").append(IPubCons._N);
            head.append(" IF tableExistedCount = 0 THEN ").append(IPubCons._N).append(IPubCons.TAB);
            head.append(parseExeImmediate(statement));

            return head;
        }

        private String parseExeImmediate(String statement)
        {
            return " EXECUTE IMMEDIATE '"
                    + statement.replace(IPubCons.SINGLE_QUOTE, "''")
                               .replace(IPubCons.QUOTE, "''")
                    + "';";
        }

        private void createPrefabField(Map< String,Object > tabMap, StringBuffer sqlScript, StringBuffer comments)
        {
            Object table = tabMap.get(DicTableVO.SDT_TABLE_EN_NAME);
            for (int i = 1; i < 16; i++)
            {
                if (i > 10) { sqlScript.append(" def").append(i).append(" NUMBER(38, 8),").append(IPubCons.CRLF); }
                else { sqlScript.append(" def").append(i).append(" VARCHAR2(250),").append(IPubCons.CRLF); }
                comments.append(String.format(ISqlCons.commentSql, table, "def" + i, "预留" + i));
            }

            String[] filds = ("Pk_group,pk_org,pk_branch,PK_workflow,cr.eateor,modifier").split(PubEnvUtil.COMMA);
            for (int i = 0; i < filds.length; i++)
            { sqlScript.append(PubEnvUtil.SPACE).append(filds[i].trim()).append(" CHAR(36),").append(IPubCons.CRLF); }

            filds = ("createon,modifiedon,ts").split(PubEnvUtil.COMMA);
            for (int i = 0; i < filds.length; i++)
            { sqlScript.append(PubEnvUtil.SPACE).append(filds[i].trim()).append(" CHAR(19),").append(IPubCons.CRLF); }

            comments.append(String.format(ISqlCons.commentSql, table, "Pk_group", "所属集团(预制)"));
            comments.append(String.format(ISqlCons.commentSql, table, "pk_org", "所属组织(预制)"));
            comments.append(String.format(ISqlCons.commentSql, table, "pk_branch", "所属院区(预制)"));
            comments.append(String.format(ISqlCons.commentSql, table, "PK_workflow", "工作流(预制)"));
            comments.append(String.format(ISqlCons.commentSql, table, "createor", "创建人(预制)"));
            comments.append(String.format(ISqlCons.commentSql, table, "modifier", "修改人(预制)"));
            comments.append(String.format(ISqlCons.commentSql, table, "createon", "创建时间yyyy-mm-dd hh:mm:ss(预制)"));
            comments.append(String.format(ISqlCons.commentSql, table, "modifiedon", "修改时间yyyy-mm-dd hh:mm:ss(预制)"));
            comments.append(String.format(ISqlCons.commentSql, table, "ts", "时间戳(预制)"));

        }

        private void createReserveFieldSql(Map< String,Object > tabMap, List< String > insertList)
        {
            Map< String,Object > fieldMap = null;
            for (int i = 1; i < 16; i++)
            {
                fieldMap = getFieldMap(tabMap);
                fieldMap.put(DicTableFieldVO.SDF_FIELD_EN_NAME, "def" + i);
                fieldMap.put(DicTableFieldVO.SDF_FIELD_ZH_NAME, "预留" + i);
                if (i > 10)
                {
                    fieldMap.put(DicTableFieldVO.SDF_FIELD_TYPE, "NUMBER");
                    fieldMap.put(DicTableFieldVO.DECIMALPRECISION, "8");
                    fieldMap.put(DicTableFieldVO.SDF_FIELD_LENGTH, "38");
                }
                else
                {
                    fieldMap.put(DicTableFieldVO.SDF_FIELD_TYPE, "VARCHAR2");
                    fieldMap.put(DicTableFieldVO.DECIMALPRECISION, "0");
                    fieldMap.put(DicTableFieldVO.SDF_FIELD_LENGTH, "250");
                }
                insertList.add(SqlHelper.getInsertSQL(DicTableFieldVO.TABLE_NAME, fieldMap));
            }

            String[] filds = ("Pk_group,pk_org,pk_branch,PK_workflow,createor,modifier ").split(PubEnvUtil.COMMA);
            for (int i = 0; i < filds.length; i++)
            {
                fieldMap = getFieldMap(tabMap);
                fieldMap.put(DicTableFieldVO.SDF_FIELD_EN_NAME, filds[i].trim());
                fieldMap.put(DicTableFieldVO.SDF_FIELD_ZH_NAME, filds[i].trim() + "(预制)");
                fieldMap.put(DicTableFieldVO.SDF_FIELD_TYPE, "CHAR");
                fieldMap.put(DicTableFieldVO.DECIMALPRECISION, "0");
                fieldMap.put(DicTableFieldVO.SDF_FIELD_LENGTH, "36");
                insertList.add(SqlHelper.getInsertSQL(DicTableFieldVO.TABLE_NAME, fieldMap));
            }

            filds = ("createon,modifiedon,ts").split(PubEnvUtil.COMMA);
            for (int i = 0; i < filds.length; i++)
            {
                fieldMap = getFieldMap(tabMap);
                fieldMap.put(DicTableFieldVO.SDF_FIELD_EN_NAME, filds[i].trim());
                fieldMap.put(DicTableFieldVO.SDF_FIELD_ZH_NAME, filds[i].trim() + "(预制)时间格式：yyyy-mm-dd hh:mm:ss");
                fieldMap.put(DicTableFieldVO.SDF_FIELD_TYPE, "CHAR");
                fieldMap.put(DicTableFieldVO.DECIMALPRECISION, "0");
                fieldMap.put(DicTableFieldVO.SDF_FIELD_LENGTH, "19");
                insertList.add(SqlHelper.getInsertSQL(DicTableFieldVO.TABLE_NAME, fieldMap));
            }

        }

        Map< String,Object > getFieldMap(Map< String,Object > tabMap)
        {
            Map< String,Object > fieldMap = MapUtil.newHashMap(Boolean.TRUE);
            setDef(tabMap, fieldMap);
            fieldMap.put(DicTableFieldVO.PRIMARY_KEY, GenerateUtil.generateID());
            fieldMap.put(DicTableFieldVO.SDF_PRIMARY_KEY, PubEnvUtil.ON);
            fieldMap.put(DicTableFieldVO.ISNOTNULL, InumberCons.ONE);
            fieldMap.put(DicTableFieldVO.ISEXTEND, InumberCons.ZERO);
            fieldMap.put(DicTableFieldVO.DEFVALUE, PubEnvUtil.WAVE);
            fieldMap.put(DicTableFieldVO.REFOBJECT, PubEnvUtil.WAVE);
            fieldMap.put(DicTableFieldVO.VERSION, InumberCons.ONE);
            fieldMap.put(DicTableFieldVO.SORT, InumberCons.ZERO);
            // fieldMap.put(DicTableFieldVO.MODIFIEDON, DateUtilbc.currentDateTime());

            return fieldMap;
        }

        private void setDef(Map< String,Object > tabMap, Map< String,Object > fieldMap)
        {
            fieldMap.put(DicTableFieldVO.PRIMARY_KEY, GenerateUtil.generateID());
            fieldMap.put(DicTableFieldVO.SDT_TABLE_EN_NAME, tabMap.get(DicTableVO.SDT_TABLE_EN_NAME));
            fieldMap.put(DicTableFieldVO.SDT_SYSTEM_NAME, tabMap.get(DicTableVO.SDT_SYSTEM_NAME));
            fieldMap.put(DicTableFieldVO.SDT_ID, tabMap.get(DicTableVO.PRIMARY_KEY));
            fieldMap.put(DicTableFieldVO.MODULEID, tabMap.get(DicTableVO.MODULEID));
            fieldMap.put(DicTableFieldVO.CREATEON, DateUtilbc.currentDateTime());
        }


    }
}
